package com.service;

import cn.hutool.core.util.StrUtil;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import com.exception.ApiException;
import com.model.DataBaseSource;
import com.model.DataInfo;
import com.model.TableColumn;
import com.model.enumerate.JavaTypeEnum;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ConcurrentHashMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author: cxt
 * @time: 2022/6/20
 */
@Service
public class GenerateService {

    private ConcurrentHashMap<String, DataBaseSource> map = new ConcurrentHashMap<>();

    /**
     * 获取数据源信息
     *
     * @return
     */
    public List<DataInfo> getSources() {
        List<DataInfo> list = new ArrayList<>();
        map.forEach((k, v) -> {
            DataInfo dataInfo = v.getDataInfo();
            list.add(dataInfo);
        });
        return list;
    }

    /**
     * 添加连接信息
     */
    public void initData(DataInfo da) {
        DataBaseSource source = map.get(da.getName());
        if (source != null) {
            source.close();
        }
        String url = da.getUrl() + "/" + da.getName();

        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(url);
        dataSource.setPassword(da.getPassword());
        dataSource.setUsername(da.getUsername());
        dataSource.setName(da.getName());
        dataSource.setMaxWait(10000);

        map.put(da.getName(), new DataBaseSource(da, dataSource));
    }

    /**
     * 删除数据库资源
     *
     * @param name
     */
    public void remove(String name) {
        DataBaseSource remove = map.remove(name);
        if (remove == null) {
            throw new ApiException("删除失败");
        }
    }

    /**
     * 初始化连接
     */
    public void connect(String dbName) {
        DataBaseSource dataSource = map.get(dbName);
        if (null == dataSource) {
            throw new ApiException("连接的数据不存在,请重试连接");
        }
        if (!dataSource.getError()) {
            dataSource.init();
        } else {
            throw new ApiException("初始化连接异常");
        }
    }

    /**
     * 查询数据表
     */
    public String getTable(String dbName) {
        DataBaseSource dataSource = map.get(dbName);
        if (null == dataSource) {
            throw new ApiException("连接的数据不存在,请重试连接");
        }
        List<String> list = null;
        String type = dataSource.getDataInfo().getType();
        switch (type) {
            case "MySQL":
                list = getMysqlTables(dataSource, null);
                break;
            case "ORACLE":
                list = getOracleTables(dataSource, null);
                break;
        }
        String join = "";
        if (list != null && list.size() != 0) {
            join = StringUtils.join(list, ",");
        }
        return join;
    }

    /**
     * 查询表字段信息
     *
     * @param tableSchema 数据名称
     * @param tableName   表名称
     * @return
     */
    public List<TableColumn> getTableColumn(String tableSchema, String tableName) {
        List<TableColumn> list = null;
        DataBaseSource source = map.get(tableSchema);
        if (source == null) {
            throw new ApiException("数据连接出错，请重试连接");
        }
        if (!source.getClosed() && source.getInitialization()) {
            String type = source.getDataInfo().getType();
            switch (type) {
                case "MySQL":
                    list = getMysqlTableInfo(source, tableName);
                    break;
                case "ORACLE":
                    list = getOracleTableInfo(source, tableName);
                    break;
            }
        } else {
            throw new ApiException("请先连接数据库再查询");
        }

        return list != null ? resultProcessing(list) : null;
    }

    /**
     * 表字段结果处理
     */
    public List<TableColumn> resultProcessing(List<TableColumn> list) {
        for (TableColumn column : list) {
            column.setJavaAttribute(StrUtil.toCamelCase(column.getName()));
            column.setQuery(false);
            String type = column.getType();
            String vale = type.split("\\(")[0];

            String intStr = "tinyintsmallintmediumintsmallintmediumint";
            if (intStr.contains(vale)) {
                column.setJavaType(JavaTypeEnum.Integer);
                continue;
            }

            String longStr = "bigint";
            if (longStr.contains(vale)) {
                column.setJavaType(JavaTypeEnum.Long);
                continue;
            }

            String str = "charvarchartinytext";
            if (str.contains(vale)) {
                column.setJavaType(JavaTypeEnum.String);
                continue;
            }

            String floatStr = "float";
            if (floatStr.contains(vale)) {
                column.setJavaType(JavaTypeEnum.Float);
                continue;
            }

            String doubleStr = "double";
            if (doubleStr.contains(vale)) {
                column.setJavaType(JavaTypeEnum.Double);
                continue;
            }

            String decimalStr = "decimal";
            if (decimalStr.contains(vale)) {
                column.setJavaType(JavaTypeEnum.BigDecimal);
                continue;
            }

            String dateStr = "datetimetimestampyear";
            if (dateStr.contains(vale)) {
                column.setJavaType(JavaTypeEnum.Date);
                continue;
            }

            String bolStr = "bit";
            if (bolStr.contains(vale)) {
                column.setJavaType(JavaTypeEnum.Boolean);
            }
        }
        return list;
    }

    /**
     * ## MYSQL
     * 查询全部表名称
     */
    public List<String> getMysqlTables(DataBaseSource dataSource, String tableName) {
        List<String> list = null;
        try {
            String sql = "SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + dataSource.getDataInfo().getName() + "' group by table_name ORDER BY TABLE_NAME";
            if (StringUtils.isNotEmpty(tableName)) {
                sql = "SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + dataSource.getDataInfo().getName() + "' AND TABLE_NAME =" + tableName;
            }
            DruidPooledConnection connection = dataSource.getDataSource().getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            list = new ArrayList<>();
            while (resultSet.next()) {
                list.add(resultSet.getString(1));
            }
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * ## mysql
     * 根据表查询表信息
     */
    public List<TableColumn> getMysqlTableInfo(DataBaseSource source, String tableName) {
        List<TableColumn> list = null;
        try {
            String sql = "SELECT TABLE_SCHEMA,TABLE_NAME ,ORDINAL_POSITION,COLUMN_NAME,COLUMN_DEFAULT ,IS_NULLABLE,COLUMN_TYPE,COLUMN_KEY,EXTRA,COLUMN_COMMENT FROM information_schema.COLUMNS  WHERE TABLE_SCHEMA = '" + source.getDataInfo().getName() + "' AND TABLE_NAME = '" + tableName + "'";
            DruidPooledConnection connection = source.getDataSource().getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            list = new ArrayList<>();
            while (resultSet.next()) {
                TableColumn table = new TableColumn();
                table.setDbName(resultSet.getString("TABLE_SCHEMA"));
                table.setTableName(resultSet.getString("TABLE_NAME"));
                table.setPosition(Integer.parseInt(resultSet.getString("ORDINAL_POSITION")));
                table.setName(resultSet.getString("COLUMN_NAME"));
                table.setDelft(resultSet.getString("COLUMN_DEFAULT"));
                table.setIsNullable(resultSet.getString("IS_NULLABLE"));
                String type = resultSet.getString("COLUMN_TYPE");
                table.setType(type);
                int length = 0;
                Matcher s = Pattern.compile("(?<=\\()(\\d+)(?=\\))").matcher(type);
                if (s.find()) {
                    length = Integer.parseInt(s.group(1));
                }
                table.setLength(length);
                table.setColumnKey(resultSet.getString("COLUMN_KEY"));
                table.setExtra(resultSet.getString("EXTRA")); //是否自增
                table.setComment(resultSet.getString("COLUMN_COMMENT"));
                list.add(table);
            }
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new ApiException("数据库连接异常", e.getMessage());
        }
        return list;
    }

    /**
     * ## ORACLE
     * 查询全部表名称
     */
    public List<String> getOracleTables(DataBaseSource dataSource, String tableName) {
        List<String> list = null;
        try {
            String sql = "SELECT DISTINCT TABLE_NAME FROM USER_TAB_COLS ORDER BY TABLE_NAME";
            if (StringUtils.isNotEmpty(tableName)) {
                sql = "SELECT DISTINCT TABLE_NAME FROM USER_TAB_COLS WHERE TABLE_NAME =" + tableName;
            }
            DruidPooledConnection connection = dataSource.getDataSource().getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            list = new ArrayList<>();
            while (resultSet.next()) {
                list.add(resultSet.getString(1));
            }
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * ## ORACLE
     * 查询表详情
     */
    public List<TableColumn> getOracleTableInfo(DataBaseSource source, String tableName) {
        List<TableColumn> list = null;
        try {
            String sql = "SELECT A.TABLE_NAME,A.COLUMN_NAME,A.DATA_TYPE,A.COLUMN_ID,A.DATA_LENGTH,A.NULLABLE,A.DATA_DEFAULT,B.COMMENTS  FROM USER_TAB_COLS A  LEFT JOIN USER_COL_COMMENTS B ON B.TABLE_NAME = A.TABLE_NAME AND B.COLUMN_NAME = A.COLUMN_NAME ORDER BY A.TABLE_NAME,COLUMN_ID";
            DruidPooledConnection connection = source.getDataSource().getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            list = new ArrayList<>();
            while (resultSet.next()) {
                TableColumn table = new TableColumn();
                table.setDbName(source.getDataInfo().getName());
                table.setTableName(resultSet.getString("TABLE_NAME"));
                table.setName(resultSet.getString("COLUMN_NAME"));
                table.setType(resultSet.getString("DATA_TYPE"));
                table.setPosition(Integer.parseInt(resultSet.getString("COLUMN_ID")));
                table.setLength(Integer.parseInt(resultSet.getString("DATA_LENGTH")));
                table.setIsNullable(resultSet.getString("NULLABLE"));
                table.setDelft(resultSet.getString("DATA_DEFAULT"));
                table.setComment(resultSet.getString("COMMENTS"));

                list.add(table);
            }
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new ApiException("数据库连接异常", e.getMessage());
        }
        return list;
    }

    /**
     * 执行没有返回值的SQL
     * 1、建表
     * 2、插入数据等
     *
     * @param source dataSource
     * @param sql    执行的SQL
     */
    public void implementSql(DataBaseSource source, String sql) {
        DruidPooledConnection connection = null;
        try {
            connection = source.getDataSource().getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            boolean execute = statement.execute();
            if (!execute) {
                throw new ApiException("执行SQL异常，请检查SQL语句");
            }
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 执行SQL，新建表
     *
     * @param sql 建表SQL
     * @return
     */
    public List<String> createTableByStr(String sql, String dbName) {
        DataBaseSource source = map.get(dbName);

        if (source == null) {
            throw new ApiException("数据连接出错，请重试连接");
        }
        List<String> list = null;
        if (!source.getClosed() && source.getInitialization()) {
            String tableName = "";
            String[] split = sql.split("\\(");
            tableName = split[0].substring(12).trim();
            Matcher s = Pattern.compile("(?<=`)(\\S+)(?=`)").matcher(tableName);
            if (s.find()) {
                tableName = s.group(1);
            }
            String type = source.getDataInfo().getType();
            switch (type) {
                case "MySQL":
                    list = getMysqlTables(source, tableName);
                    if (list.size() != 0) {
                        throw new ApiException("新建表已存在");
                    }
                    // 建表
                    implementSql(source, sql);
                    list = getMysqlTables(source, null);
                    break;
                case "ORACLE":
                    list = getOracleTables(source, tableName);
                    if (list.size() != 0) {
                        throw new ApiException("新建表已存在");
                    }
                    // 建表
                    implementSql(source, sql);
                    list = getOracleTables(source, null);
                    break;
            }
        } else {
            throw new ApiException("请先连接数据库再执行新建表");
        }
        return list;
    }
}
